package com.fare.taluo.fetl.domain.repository.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.fare.taluo.fetl.domain.base.EntityFetlHibernateRepository;
import com.fare.taluo.fetl.domain.entity.PlaneInfoJsonEntity;
import com.fare.taluo.fetl.domain.entity.SinglePlaneInfoEntity;
import com.fare.taluo.fetl.domain.repository.ISinglePlaneInfoRepository;
import com.fare.taluo.util.DateUtils;

@Repository("singlePlaneInfoRepository")
public class SinglePlaneInfoRepositoryImpl extends
		EntityFetlHibernateRepository implements
		ISinglePlaneInfoRepository {

	@Override
	public List<SinglePlaneInfoEntity> findPlaneInfoProperty(String hbaseKey,
			String fromCity, String toCity, String flightBatchDate,
			String flightNo) {
		StringBuffer jpql =new StringBuffer("select plane from SinglePlaneInfoEntity plane where plane.grabDate>=? ");
		List<Object> conditionVals = new ArrayList<Object>();
		conditionVals.add(DateUtils.getCurrentDateStartTime());
		if(null!=hbaseKey && !"".equals(hbaseKey)){
			jpql.append(" and plane.attachHbaseKey =? ");
			conditionVals.add(hbaseKey);
		}
		if(null!=fromCity && !"".equals(fromCity)){
			jpql.append(" and plane.fromCity =? ");
			conditionVals.add(fromCity);
		}
		if(null!=toCity && !"".equals(toCity)){
			jpql.append(" and plane.toCity =? ");
			conditionVals.add(toCity);
		}
		if(null!=flightNo && !"".equals(flightNo)){
			jpql.append(" and plane.flightNo =? ");
			conditionVals.add(flightNo);
		}
		if(null!=flightBatchDate && !"".equals(flightBatchDate)){
			jpql.append(" and plane.flightDate =? ");
			conditionVals.add(flightBatchDate);
		}
		jpql.append(" order by plane.attachHbaseKey,plane.flightDate,plane.fromCity,plane.toCity,plane.flightNo");
		List<SinglePlaneInfoEntity> list =null;
		Session session =getSessionFactory1().openSession();
		try {
			Query query = session.createQuery(jpql.toString());
			for (int i = 0; i < conditionVals.size(); i++) {
				query = query.setParameter(i, conditionVals.get(i));
			}
			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		return list;
	}
	private final static String QUERY_SQL_PLANEINFO = "select p.ID,p.SOURCE_ROWKEY,p.ATTACH_HBASE_KEY,p.GRAB_DATE,p.CREATE_TIME,p.GRAB_CHANNEL_ID,"+
			"p.GRAB_CHANNEL_NAME,p.CARRIER_KEY,p.CARRIER_NAME,p.CARRIER_FULL_NAME,p.FLIGHTNO,p.ACTUALLY_FLIGHT_NO,"+
			"p.FLIGHT_TYPE,p.FLIGHT_DATE,p.START_TIME,p.END_TIME,p.FROM_CITY,p.FROM_CITY_NAME,p.TO_CITY,"+
			"p.TO_CITY_NAME,p.AREA_CODE,p.AREA_NAME,p.TOTAL_HIGHEST_PRICE,p.TOTAL_LOWEST_PRICE,p.AGENT_NAME,j.ID as json_id,"+
			"j.AGENTS_JSON,j.CABINS_JSON,j.TRANSITS_JSON,j.STOPOVERS_JSON from planeinfo p left join planeinfo_json j on p.json_id=j.id ";

	@SuppressWarnings("unchecked")
	@Override
	public List<SinglePlaneInfoEntity> findPlaneInfoByCondition(String grabChannel,String carrierKey,
			String fromCity, String toCity, String flightBatchDate,
			String flightNo) {
		StringBuffer jpql =new StringBuffer(QUERY_SQL_PLANEINFO);
		jpql.append(" where p.GRAB_DATE>=? ");
		List<Object> conditionVals = new ArrayList<Object>();
		conditionVals.add(DateUtils.getCurrentDateStartTime());
		if(StringUtils.isNotBlank(grabChannel)){
			jpql.append(" and p.ATTACH_HBASE_KEY =? ");
			conditionVals.add(grabChannel);
		}
		if(null!=carrierKey && !"".equals(carrierKey)){
			jpql.append(" and p.CARRIER_KEY =? ");
			conditionVals.add(carrierKey);
		}
		if(StringUtils.isNotBlank(fromCity)){
			jpql.append(" and p.FROM_CITY =? ");
			conditionVals.add(fromCity);
		}
		if(StringUtils.isNotBlank(toCity)){
			jpql.append(" and p.TO_CITY =? ");
			conditionVals.add(toCity);
		}
		if(StringUtils.isNotBlank(flightNo)){
			jpql.append(" and p.FLIGHTNO =? ");
			conditionVals.add(flightNo);
		}
		if(StringUtils.isNotBlank(flightBatchDate)){
			jpql.append(" and p.FLIGHT_DATE =? ");
			conditionVals.add(flightBatchDate);
		}
		jpql.append(" order by p.ATTACH_HBASE_KEY,p.FLIGHT_DATE,p.FROM_CITY,p.TO_CITY,p.FLIGHTNO");
		
		List<SinglePlaneInfoEntity> list =null;
		List<Object[]> results = null;
		SinglePlaneInfoEntity spe = null;
		Session session =getSessionFactory1().openSession();
		try {
			list = new ArrayList<SinglePlaneInfoEntity>();
			Query query = session.createSQLQuery(jpql.toString());
			for (int i = 0; i < conditionVals.size(); i++) {
				query = query.setParameter(i, conditionVals.get(i));
			}
			results = query.list();
			if(results != null && !results.isEmpty()){
				for(Object[] o : results){
					spe = this.convertPlaneInfoEntity(o);

					list.add(spe);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		return list;
	}

	public SinglePlaneInfoEntity convertPlaneInfoEntity(Object[] objs){
		if(objs == null || objs.length<=0) return null;
		SinglePlaneInfoEntity entity = new SinglePlaneInfoEntity();
		entity.setId(Long.valueOf((objs[0]).toString()));
		entity.setSourceRowkey((String)objs[1]);
		entity.setAttachHbaseKey((String)objs[2]);
		entity.setGrabDate((Date)objs[3]);
		entity.setCreateTime((Date)objs[4]);
		entity.setGrabChannelId(Long.valueOf((objs[5]).toString()));
		entity.setGrabChannelName((String)objs[6]);
		entity.setCarrierKey((String)objs[7]);
		entity.setCarrierName((String)objs[8]);
		entity.setCarrierFullName((String)objs[9]);
		entity.setFlightNo((String)objs[10]);
		entity.setActuallyFlightNo((String)objs[11]);
		entity.setFlightType((String)objs[12]);
		entity.setFlightDate((String)objs[13]);
		entity.setStartTime((Date)objs[14]);
		entity.setEndTime((Date)objs[15]);
		entity.setFromCity((String)objs[16]);
		entity.setFromCityName((String)objs[17]);
		entity.setToCity((String)objs[18]);
		entity.setToCityName((String)objs[19]);
		entity.setAreaCode((String)objs[20]);
		entity.setAreaName((String)objs[21]);
		entity.setTotalHighestPrice((Double)objs[22]);
		entity.setTotalLowestPrice((Double)objs[23]);
		entity.setAgentName((String)objs[24]);
		if(objs[25] != null){
			PlaneInfoJsonEntity jsonEntity = new PlaneInfoJsonEntity();
			jsonEntity.setId(Long.valueOf((objs[25]).toString()));
			jsonEntity.setAgentsJson((String)objs[26]);
			jsonEntity.setCabinsJson((String)objs[27]);
			jsonEntity.setTransitsJson((String)objs[28]);
			jsonEntity.setStopOversJson((String)objs[29]);

			entity.setPlaneInfoJsonEntity(jsonEntity);
		}
		return entity;
	}
	
	@Override
	public List<SinglePlaneInfoEntity> findPlaneInfoPropertyAll(String hbaseKey,
			String fromCity, String toCity, String flightBatchDate,
			String flightNo) {
		StringBuffer jpql =new StringBuffer(QUERY_SQL_PLANEINFO);
		jpql.append(" where p.GRAB_DATE>=? ");
		List<Object> conditionVals = new ArrayList<Object>();
		conditionVals.add(DateUtils.getCurrentDateStartTime());
		if(StringUtils.isNotBlank(hbaseKey)){
			jpql.append(" and p.ATTACH_HBASE_KEY =? ");
			conditionVals.add(hbaseKey);
		}
		if(StringUtils.isNotBlank(fromCity)){
			jpql.append(" and p.FROM_CITY =? ");
			conditionVals.add(fromCity);
		}
		if(StringUtils.isNotBlank(toCity)){
			jpql.append(" and p.TO_CITY =? ");
			conditionVals.add(toCity);
		}
		if(StringUtils.isNotBlank(flightNo)){
			jpql.append(" and p.FLIGHTNO =? ");
			conditionVals.add(flightNo);
		}
		if(StringUtils.isNotBlank(flightBatchDate)){
			jpql.append(" and p.FLIGHT_DATE =? ");
			conditionVals.add(flightBatchDate);
		}
		jpql.append(" order by p.ATTACH_HBASE_KEY,p.FLIGHT_DATE,p.FROM_CITY,p.TO_CITY,p.START_TIME");
		List<SinglePlaneInfoEntity> list =null;
		List<Object[]> results = null;
		SinglePlaneInfoEntity spe = null;
		Session session =getSessionFactory1().openSession();
		try {
			list = new ArrayList<SinglePlaneInfoEntity>();
			Query query = session.createSQLQuery(jpql.toString());
			for (int i = 0; i < conditionVals.size(); i++) {
				query = query.setParameter(i, conditionVals.get(i));
			}
			results = query.list();
			if(results != null && !results.isEmpty()){
				for(Object[] o : results){
					spe = this.convertPlaneInfoEntity(o);

					list.add(spe);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		return list;
	}
	
	@Override
	public List<SinglePlaneInfoEntity> findPlaneInfoPropertyAll(String hbaseKey,String carrierKey,
			String fromCity, String toCity, String flightBatchDate,
			String flightNo) {
		StringBuffer jpql =new StringBuffer(QUERY_SQL_PLANEINFO);
		jpql.append(" where p.GRAB_DATE>=? ");
		List<Object> conditionVals = new ArrayList<Object>();
		conditionVals.add(DateUtils.getCurrentDateStartTime());
		if(StringUtils.isNotBlank(hbaseKey)){
			jpql.append(" and p.ATTACH_HBASE_KEY =? ");
			conditionVals.add(hbaseKey);
		}
		if(StringUtils.isNotBlank(carrierKey)){
			jpql.append(" and p.CARRIER_KEY =? ");
			conditionVals.add(carrierKey);
		}
		if(StringUtils.isNotBlank(fromCity)){
			jpql.append(" and p.FROM_CITY =? ");
			conditionVals.add(fromCity);
		}
		if(StringUtils.isNotBlank(toCity)){
			jpql.append(" and p.TO_CITY =? ");
			conditionVals.add(toCity);
		}
		if(StringUtils.isNotBlank(flightNo)){
			jpql.append(" and p.FLIGHTNO =? ");
			conditionVals.add(flightNo);
		}
		if(StringUtils.isNotBlank(flightBatchDate)){
			jpql.append(" and p.FLIGHT_DATE =? ");
			conditionVals.add(flightBatchDate);
		}
		jpql.append(" order by p.ATTACH_HBASE_KEY,p.FLIGHT_DATE,p.FROM_CITY,p.TO_CITY,p.FLIGHTNO");
		List<SinglePlaneInfoEntity> list =null;
		List<Object[]> results = null;
		SinglePlaneInfoEntity spe = null;
		Session session =getSessionFactory1().openSession();
		try {
			list = new ArrayList<SinglePlaneInfoEntity>();
			//Query query = session.createQuery(jpql.toString());
			Query query = session.createSQLQuery(jpql.toString());
			for (int i = 0; i < conditionVals.size(); i++) {
				query = query.setParameter(i, conditionVals.get(i));
			}
			results = query.list();
			if(results != null && !results.isEmpty()){
				for(Object[] o : results){
					spe = this.convertPlaneInfoEntity(o);

					list.add(spe);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		return list;
	}
	
	@Override
	public PlaneInfoJsonEntity getJsonEntity(Long jsonId) {
		PlaneInfoJsonEntity entity = null;
		Session session =getSessionFactory1().openSession();
		try {
			Query query = session.createQuery("from PlaneInfoJsonEntity where id=?");
			query.setLong(1, jsonId);
			entity = (PlaneInfoJsonEntity)query.uniqueResult();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		return entity;
	}

}
